Rethinking PostgreSQL Performance in the Age of Monster Hardware

PGconf.dev
2025-05-15

NUMA and Sub-NUMA

• Non-Uniform Memory Access
• Increased latency
• PostgreSQL isn't NUMA-aware
• There are performance implications

What is NUMA?

• Each CPU socket has its own local memory
• Each CPU can access local or remote memory
• Access time varies (2 times slower for remote)
• Modern servers can have 2 NUMA nodes

NUMA Architecture Visualization

NUMA Architecture Visualization

What is Sub-NUMA?

• Division within a CPU socket
• L3 Cache memory is shared with several cores
• L3 Cache is divided in sub-NUMA zones
• Adds another layer NUMA-like problem

A modern CPU

How the others do it

• Oracle: NUMA-aware memory and processes
• SQL Server: Soft-NUMA
• DB2: full NUMA support
• MariaDB and PostgreSQL: None 🤷‍♀️

Current PostgreSQL Workarounds

• Using numactl
• Using systemd with CPUAffinity, NUMAPolicy, NUMAMask
• Limiting connections per instance
• Reducing shared_buffers
• Using only one socket per host

Current PostgreSQL Workarounds

# Example: Start PostgreSQL bound to NUMA node 0
numactl --cpunodebind=0 --membind=0 -- pg_ctl start

# Example: Start PostgreSQL with balancing NUMA node usage on all nodes
numactl --interleave=all -- pg_ctl start

Partitioned Shared Buffers

• Divide shared_buffers
• Allocate the "right" buffers
• Coordinate buffer management across partitions
• Balance memory usage across nodes

Implementation Challenges

• Significant changes
• Complex coordination between partitions
• Maintaining buffer replacement policy
• Risk of introducing instability
• Testing complexity
• Backward compatibility concerns

Is NUMA Worth It?

• AMD: stop sub-NUMA optimization
• Modern CPUs have 386+ threads
• Interconnect speeds are improving
• Code complexity vs. performance gain

NUMA Observability

• New in Postgres 18
• In pg_buffercache
• Integration with libnuma
• Future optimization capabilities

NUMA Observability in PG18

-- Identifying buffer distribution across NUMA nodes
select page_num,node_id,count(*)
from pg_buffercache_numa
group by page_num,node_id
order by 1
limit 3;

page_num | node_id | count
----------+---------+-------
        0 |       1 |  1024
        1 |       0 |  1024
        2 |       1 |  1024

Thread versus Processes

• Multi-process architecture
• Each connection forks postnaster
• Modern hardware challenges this
• Threading models = better performance ?
• A lot of community discussions

Process Model Limitations

• Each connection creates a process
• Higher overhead
• Sharing memory difficulty
• Connection pooling mandatory

Other Databases

• Oracle: Dedicated or shared server processes
• SQL Server: Thread pool model
• MariaDB: Thread-per-connection

Current Community Discussions

• Heikki Linnakangas' talk
• Active wiki page
• Thomas Monroe's talk

Community Consensus

Multi-threaded programming model would be superior

Robert Haas, hackers mailing-list, 2024-06-05

• Performance improvements
• New features
• Slow transition

Technical Challenges

• State management across threads
• Thread-local storage for session state
• Synchronization primitives and locking
• Signal handling in threaded environment
• Memory management and allocation
• Build-time vs. runtime configuration options

Potential Performance Benefits

• Lower memory overhead
• Reduced context switching costs
• More efficient cache utilization
• Reduced latency for short transactions
• More efficient on monster hardware
• No more need for pooler?

Is It Worth it?

• I/O is the bottlenecks
• Storage technology is slow
• CPU and RAM are fast
• what's the best benefits/efforts ratio?

Real Bottlenecks on Monster Hardware

• Storage I/O bottleneck
• Network bandwidth
• Lock contention
• Memory bandwidth
• Cache coherency overhead with many cores

Performance Hierarchy

  Access Times (log scale)

  L1 Cache  │█                                           │ 1ns      (4 cycles)
  L2 Cache  │████                                        │ 4ns      (12 cycles)
  L3 Cache  │████████████                                │ 12ns     (40 cycles)
  RAM       │████████████████████████████████████        │ 100ns    (300 cycles)
  NVMe SSD  │██████████████████████████████████████████████████████████████████████████████████████████████████│ 20,000ns  (60K cycles)
  SATA SSD  │████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████│ 100,000ns (300K cycles)
  

Where CPU Still Matters

• Complex query execution
• JSON/JSONB processing
• Encryption/decryption operations
• Text search and pattern matching
• Window functions and analytical queries

On the field priorities

• Datamodeling
• SQL query writing
• PostgreSQL tuning
→ Training devs and users

Balancing Optimization Efforts

• CPU: specific workloads
• Memory layout: large datasets
• Storage performance: highest ROI for all
• Architectural changes: highest potential

Conclusion: PostgreSQL on Monster Hardware

• NUMA awareness is coming
• Process-to-thread might come
• Storage I/O remains the true bottleneck

Conclusion: PostgreSQL on Monster Hardware

• Vertical scaling with monster servers is simpler
• Do not decide on assumed limitations
• PostgreSQL continues to evolve

Questions?

Thank You!

Bluesky: @l-avrot.bsky.social

Linkedin: lætitia-avrot